import java.sql.*;

/****************************************************************************** 
 * The <code>DB</code> class establishes database connection.  
 * Methods are provided to query and update the table data. 
 * @author Team 4
 *****************************************************************************/ 

public class DB {
	private Connection connect = null;
	private String dbURL = "jdbc:mysql://157.62.23.204/p3t4";
	private String username = "p3t4";
	private String password = "p3t4";

    /*************************************************************************** 
    * Creates an <code>DB</code> object 
    **************************************************************************/ 
	public DB() {
		getConnection();
	}
    
    /*************************************************************************** 
    * Establishes a database connection 
    **************************************************************************/ 
	private void getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connect = DriverManager.getConnection(dbURL, username, password);
		}
		catch (ClassNotFoundException e) {
			System.out.println("Database driver not found");
		}
		catch (Exception e) {
			System.out.println("Other Exception");
		}
	}
    
    /*************************************************************************** 
    * Performs a database table query  
    * @param q A <code>String</code> specifying the query parameters.  
    * @return rs A <code>ResultSet</code> specifying the query result information. 
    **************************************************************************/ 
	public ResultSet DBquery(String q) {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = connect.prepareStatement(q);
			rs = ps.executeQuery();
		}
		catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		catch (Exception e) {
			System.out.println("Other Exception");
		}
		return rs;
	}

    /***************************************************************************
    * Performs a database table update
    * @param q A <code>String</code> specifying the update parameters.
    **************************************************************************/
	public void DBedit(String q) {
		PreparedStatement ps = null;
		try {
			ps = connect.prepareStatement(q);
			ps.executeUpdate();
		}
		catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		catch (Exception e) {
			System.out.println("Other Exception");
		}
	}
        
        /*************************************************************************** 
        * Begin of the DB class. 
        * @param args A <code>String array</code> not used by this application. 
        **************************************************************************/ 
        public static void main(String[] args) throws Exception {
		DB newdb = new DB();
		String preparedSQL = "select * from transaction";
		ResultSet rs = newdb.DBquery(preparedSQL);
		while (rs.next()) {
			System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) 
            + "\t" + rs.getString(4) + "\t" + rs.getBigDecimal(5) + "\t" + rs.getBigDecimal(6));
		}
	}
}
